using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ExcelTemplateEngine.OpenXML
{
    /// <summary>
    /// 基于OpenXML的工作表实现
    /// </summary>
    public class OpenXMLWorksheet : IWorksheet
    {
        private readonly WorksheetPart _worksheetPart;
        private readonly OpenXMLWorkbook _workbook;
        private string _name;
        
        /// <summary>
        /// 内部的WorksheetPart对象
        /// </summary>
        internal WorksheetPart WorksheetPart => _worksheetPart;
        
        /// <summary>
        /// 关联的工作簿
        /// </summary>
        internal OpenXMLWorkbook Workbook => _workbook;
        
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="worksheetPart">工作表部件</param>
        /// <param name="name">工作表名称</param>
        /// <param name="workbook">关联的工作簿</param>
        public OpenXMLWorksheet(WorksheetPart worksheetPart, string name, OpenXMLWorkbook workbook)
        {
            _worksheetPart = worksheetPart ?? throw new ArgumentNullException(nameof(worksheetPart));
            _name = name ?? throw new ArgumentNullException(nameof(name));
            _workbook = workbook ?? throw new ArgumentNullException(nameof(workbook));
        }
        
        /// <summary>
        /// 工作表名称
        /// </summary>
        public string Name
        {
            get => _name;
            set
            {
                if (string.IsNullOrEmpty(value))
                    throw new ArgumentException("工作表名称不能为空", nameof(value));
                _name = value;
            }
        }
        
        /// <summary>
        /// 获取单元格
        /// </summary>
        /// <param name="address">单元格地址，如"A1"</param>
        /// <returns>单元格对象</returns>
        public ICell GetCell(string address)
        {
            if (string.IsNullOrEmpty(address))
                throw new ArgumentException("单元格地址不能为空", nameof(address));
                
            if (!CellAddressHelper.IsValidAddress(address))
                throw new ArgumentException($"无效的单元格地址: {address}", nameof(address));
                
            var sheetData = GetOrCreateSheetData();
            var (row, column) = CellAddressHelper.ParseAddress(address);
            
            var rowElement = GetOrCreateRow(sheetData, row);
            var cellElement = GetOrCreateCell(rowElement, address);
            
            return new OpenXMLCell(cellElement, this);
        }
        
        /// <summary>
        /// 获取单元格
        /// </summary>
        /// <param name="row">行号（1-based）</param>
        /// <param name="column">列号（1-based）</param>
        /// <returns>单元格对象</returns>
        public ICell GetCell(int row, int column)
        {
            var address = CellAddressHelper.CreateAddress(row, column);
            return GetCell(address);
        }
        
        /// <summary>
        /// 获取范围内的所有单元格
        /// </summary>
        /// <param name="range">范围地址，如"A1:B10"</param>
        /// <returns>单元格集合</returns>
        public IEnumerable<ICell> GetCells(string range)
        {
            if (string.IsNullOrEmpty(range))
                throw new ArgumentException("范围地址不能为空", nameof(range));
                
            if (!CellAddressHelper.IsValidRange(range))
                throw new ArgumentException($"无效的范围地址: {range}", nameof(range));
                
            var (startCell, endCell) = CellAddressHelper.ParseRange(range);
            var (startRow, startColumn) = CellAddressHelper.ParseAddress(startCell);
            var (endRow, endColumn) = CellAddressHelper.ParseAddress(endCell);
            
            var cells = new List<ICell>();
            
            for (int row = startRow; row <= endRow; row++)
            {
                for (int col = startColumn; col <= endColumn; col++)
                {
                    cells.Add(GetCell(row, col));
                }
            }
            
            return cells;
        }
        
        /// <summary>
        /// 插入行
        /// </summary>
        /// <param name="startRow">起始行号（1-based）</param>
        /// <param name="count">插入行数</param>
        public void InsertRows(int startRow, int count)
        {
            if (startRow < 1)
                throw new ArgumentException("行号必须大于0", nameof(startRow));
            if (count < 1)
                throw new ArgumentException("插入行数必须大于0", nameof(count));
                
            var sheetData = GetOrCreateSheetData();
            var rows = sheetData.Elements<Row>().Where(r => r.RowIndex!.Value >= startRow).ToList();
            
            // 更新现有行的行号
            foreach (var row in rows)
            {
                row.RowIndex = row.RowIndex!.Value + (uint)count;
                
                // 更新行中所有单元格的地址
                foreach (var cell in row.Elements<Cell>())
                {
                    if (!string.IsNullOrEmpty(cell.CellReference))
                    {
                        var (rowNum, colNum) = CellAddressHelper.ParseAddress(cell.CellReference!);
                        cell.CellReference = CellAddressHelper.CreateAddress(rowNum + count, colNum);
                    }
                }
            }
        }
        
        /// <summary>
        /// 插入列
        /// </summary>
        /// <param name="startColumn">起始列号（1-based）</param>
        /// <param name="count">插入列数</param>
        public void InsertColumns(int startColumn, int count)
        {
            if (startColumn < 1)
                throw new ArgumentException("列号必须大于0", nameof(startColumn));
            if (count < 1)
                throw new ArgumentException("插入列数必须大于0", nameof(count));
                
            var sheetData = GetOrCreateSheetData();
            
            // 更新所有受影响的单元格地址
            foreach (var row in sheetData.Elements<Row>())
            {
                var cellsToUpdate = row.Elements<Cell>()
                    .Where(c => !string.IsNullOrEmpty(c.CellReference))
                    .ToList();
                    
                foreach (var cell in cellsToUpdate)
                {
                    var (rowNum, colNum) = CellAddressHelper.ParseAddress(cell.CellReference!);
                    if (colNum >= startColumn)
                    {
                        cell.CellReference = CellAddressHelper.CreateAddress(rowNum, colNum + count);
                    }
                }
            }
        }
        
        /// <summary>
        /// 删除行
        /// </summary>
        /// <param name="startRow">起始行号（1-based）</param>
        /// <param name="count">删除行数</param>
        public void DeleteRows(int startRow, int count)
        {
            if (startRow < 1)
                throw new ArgumentException("行号必须大于0", nameof(startRow));
            if (count < 1)
                throw new ArgumentException("删除行数必须大于0", nameof(count));
                
            var sheetData = GetOrCreateSheetData();
            var endRow = startRow + count - 1;
            
            // 删除指定范围内的行
            var rowsToDelete = sheetData.Elements<Row>()
                .Where(r => r.RowIndex!.Value >= startRow && r.RowIndex!.Value <= endRow)
                .ToList();
                
            foreach (var row in rowsToDelete)
            {
                row.Remove();
            }
            
            // 更新后续行的行号
            var rowsToUpdate = sheetData.Elements<Row>()
                .Where(r => r.RowIndex!.Value > endRow)
                .ToList();
                
            foreach (var row in rowsToUpdate)
            {
                row.RowIndex = row.RowIndex!.Value - (uint)count;
                
                // 更新行中所有单元格的地址
                foreach (var cell in row.Elements<Cell>())
                {
                    if (!string.IsNullOrEmpty(cell.CellReference))
                    {
                        var (rowNum, colNum) = CellAddressHelper.ParseAddress(cell.CellReference!);
                        cell.CellReference = CellAddressHelper.CreateAddress(rowNum - count, colNum);
                    }
                }
            }
        }
        
        /// <summary>
        /// 删除列
        /// </summary>
        /// <param name="startColumn">起始列号（1-based）</param>
        /// <param name="count">删除列数</param>
        public void DeleteColumns(int startColumn, int count)
        {
            if (startColumn < 1)
                throw new ArgumentException("列号必须大于0", nameof(startColumn));
            if (count < 1)
                throw new ArgumentException("删除列数必须大于0", nameof(count));
                
            var sheetData = GetOrCreateSheetData();
            var endColumn = startColumn + count - 1;
            
            // 删除指定列范围内的单元格，并更新其他单元格的地址
            foreach (var row in sheetData.Elements<Row>())
            {
                var cellsToDelete = row.Elements<Cell>()
                    .Where(c => !string.IsNullOrEmpty(c.CellReference))
                    .Where(c =>
                    {
                        var (_, colNum) = CellAddressHelper.ParseAddress(c.CellReference!);
                        return colNum >= startColumn && colNum <= endColumn;
                    })
                    .ToList();
                    
                foreach (var cell in cellsToDelete)
                {
                    cell.Remove();
                }
                
                var cellsToUpdate = row.Elements<Cell>()
                    .Where(c => !string.IsNullOrEmpty(c.CellReference))
                    .ToList();
                    
                foreach (var cell in cellsToUpdate)
                {
                    var (rowNum, colNum) = CellAddressHelper.ParseAddress(cell.CellReference!);
                    if (colNum > endColumn)
                    {
                        cell.CellReference = CellAddressHelper.CreateAddress(rowNum, colNum - count);
                    }
                }
            }
        }
        
        /// <summary>
        /// 获取最大行号
        /// </summary>
        public int MaxRow
        {
            get
            {
                var sheetData = GetOrCreateSheetData();
                var rows = sheetData.Elements<Row>();
                return rows.Any() ? (int)rows.Max(r => r.RowIndex!.Value) : 0;
            }
        }
        
        /// <summary>
        /// 获取最大列号
        /// </summary>
        public int MaxColumn
        {
            get
            {
                var sheetData = GetOrCreateSheetData();
                var maxColumn = 0;
                
                foreach (var row in sheetData.Elements<Row>())
                {
                    foreach (var cell in row.Elements<Cell>())
                    {
                        if (!string.IsNullOrEmpty(cell.CellReference))
                        {
                            var (_, colNum) = CellAddressHelper.ParseAddress(cell.CellReference!);
                            if (colNum > maxColumn)
                                maxColumn = colNum;
                        }
                    }
                }
                
                return maxColumn;
            }
        }
        
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="range">合并范围，如"A1:B2"</param>
        public void MergeCells(string range)
        {
            if (string.IsNullOrEmpty(range))
                throw new ArgumentException("合并范围不能为空", nameof(range));
                
            if (!CellAddressHelper.IsValidRange(range))
                throw new ArgumentException($"无效的范围地址: {range}", nameof(range));
                
            var worksheet = _worksheetPart.Worksheet;
            var mergeCells = worksheet.GetFirstChild<MergeCells>();
            
            if (mergeCells == null)
            {
                mergeCells = new MergeCells();
                worksheet.InsertAfter(mergeCells, worksheet.GetFirstChild<SheetData>());
            }
            
            var mergeCell = new MergeCell() { Reference = range };
            mergeCells.Append(mergeCell);
            
            mergeCells.Count = (uint)mergeCells.Elements<MergeCell>().Count();
        }
        
        /// <summary>
        /// 取消合并单元格
        /// </summary>
        /// <param name="range">取消合并的范围</param>
        public void UnmergeCells(string range)
        {
            if (string.IsNullOrEmpty(range))
                throw new ArgumentException("范围不能为空", nameof(range));
                
            var worksheet = _worksheetPart.Worksheet;
            var mergeCells = worksheet.GetFirstChild<MergeCells>();
            
            if (mergeCells != null)
            {
                var mergeCell = mergeCells.Elements<MergeCell>()
                    .FirstOrDefault(mc => mc.Reference == range);
                    
                if (mergeCell != null)
                {
                    mergeCell.Remove();
                    mergeCells.Count = (uint)mergeCells.Elements<MergeCell>().Count();
                    
                    // 如果没有合并单元格了，删除整个MergeCells元素
                    if (mergeCells.Count == 0)
                    {
                        mergeCells.Remove();
                    }
                }
            }
        }
        
        /// <summary>
        /// 获取所有合并区域
        /// </summary>
        /// <returns>合并区域地址集合</returns>
        public IEnumerable<string> GetMergedRegions()
        {
            var worksheet = _worksheetPart.Worksheet;
            var mergeCells = worksheet.GetFirstChild<MergeCells>();
            
            if (mergeCells == null)
                return Enumerable.Empty<string>();
                
            return mergeCells.Elements<MergeCell>()
                .Select(mc => mc.Reference!.Value!)
                .Where(r => !string.IsNullOrEmpty(r));
        }
        
        /// <summary>
        /// 获取或创建SheetData元素
        /// </summary>
        /// <returns>SheetData元素</returns>
        private SheetData GetOrCreateSheetData()
        {
            var sheetData = _worksheetPart.Worksheet.GetFirstChild<SheetData>();
            if (sheetData == null)
            {
                sheetData = new SheetData();
                _worksheetPart.Worksheet.AppendChild(sheetData);
            }
            return sheetData;
        }
        
        /// <summary>
        /// 获取或创建行
        /// </summary>
        /// <param name="sheetData">工作表数据</param>
        /// <param name="rowIndex">行索引</param>
        /// <returns>行元素</returns>
        private Row GetOrCreateRow(SheetData sheetData, int rowIndex)
        {
            var row = sheetData.Elements<Row>()
                .FirstOrDefault(r => r.RowIndex!.Value == rowIndex);
                
            if (row == null)
            {
                row = new Row() { RowIndex = (uint)rowIndex };
                
                // 按行号顺序插入
                var nextRow = sheetData.Elements<Row>()
                    .FirstOrDefault(r => r.RowIndex!.Value > rowIndex);
                    
                if (nextRow != null)
                {
                    sheetData.InsertBefore(row, nextRow);
                }
                else
                {
                    sheetData.Append(row);
                }
            }
            
            return row;
        }
        
        /// <summary>
        /// 获取或创建单元格
        /// </summary>
        /// <param name="row">行元素</param>
        /// <param name="address">单元格地址</param>
        /// <returns>单元格元素</returns>
        private Cell GetOrCreateCell(Row row, string address)
        {
            var cell = row.Elements<Cell>()
                .FirstOrDefault(c => c.CellReference == address);
                
            if (cell == null)
            {
                cell = new Cell() { CellReference = address };
                
                // 按列顺序插入
                var (_, column) = CellAddressHelper.ParseAddress(address);
                var nextCell = row.Elements<Cell>()
                    .FirstOrDefault(c =>
                    {
                        if (string.IsNullOrEmpty(c.CellReference))
                            return false;
                        var (_, nextCol) = CellAddressHelper.ParseAddress(c.CellReference!);
                        return nextCol > column;
                    });
                    
                if (nextCell != null)
                {
                    row.InsertBefore(cell, nextCell);
                }
                else
                {
                    row.Append(cell);
                }
            }
            
            return cell;
        }
    }
}